const express = require('express')
const router = express.Router()
const mysql = require('mysql')
const { filterQuery, pinjieSql, filterArrs, recursion, pinjiemohu, deleteMore, createIds, pinjieEdit } = require('../util/tool')
const { status200, status401, status500 } = require('../status')
const {env}=require('../dataBase')
// const env = {
//     host: "111.231.22.97",
//     user: "cqbase",
//     password: "MZM5j38RNCjdPRP7",
//     database: "cqbase", //数据库名字
//     multipleStatements: true //允许多条sql语句查询
// }



//获取比赛列表
router.get('/getMatchList', async (req, res) => {
    const arrs = await filterQuery(req.query)
    console.log(arrs, 'arrs')
    let pool = mysql.createPool(env)
    if (arrs.length == 0) {
        console.log('全部查询')
        pool.query('select * from matchlist  ORDER BY date DESC', (err, result) => {
            try {
                status200(res, { msg: 'success', data: { data: result, total: result.length } })
            } catch {
                res.status(500)
            }
        })
    }
    else {
        pool.query('select * from matchlist', (err, result) => {
            try {
                let arr = []
                result.forEach(els => {
                    if (els.date.indexOf(req.query['date']) != -1) {
                        arr.push(els)
                    }
                });
                status200(res, { msg: 'success', data: { data: arr, total: arr.length } })
            }
            catch {
                res.status(500)
                console.log(err, 'error')
            }
        })
    }
})




//新增比赛
const postMatchList = (req, res) => {
    let pool = mysql.createPool(env)
    let { homeTeam, guestTeam, date, matchCode, homeId, guestId } = req.body
    let arrs = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
    let ids = createIds(arrs)
    let queryArr = [ids, homeTeam, guestTeam, date, matchCode, '', '', homeId, guestId]
    let queryArrs = [ids, '', '', '', '', '', '', '', '']
    pool.query('insert into matchlist values(?,?,?,?,?,?,?,?,?);insert into matchdetail values(?,?,?,?,?,?,?,?,?)', [...queryArr, ...queryArrs], (err, result) => {
        try {
            if (result[0].affectedRows >= 1 && result[1].affectedRows >= 1) {
                status200(res, { msg: '新增成功' })
            }
            else {
                status200(res, { msg: '新增失败' })
            }
        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}
router.post('/postMatchList', (req, res) => {
    postMatchList(req, res)
})


//删除比赛
const deleteMatch = (req, res) => {
    let pool = mysql.createPool(env)
    pool.query('delete from matchlist where id = ?;delete from matchdetail where id = ?', [req.body['id'], req.body['id']], (err, resp) => {
        try {
            if (resp[0].affectedRows >= 1 && resp[1].affectedRows >= 1) {
                status200(res, { msg: "删除成功" })
            }
            else {
                status500(res, { msg: '删除失败' })
            }

        } catch {
            res.status(500)
            console.log(err, 'error')
        }
    })
}
router.post('/deleteMatch', (req, res) => {
    deleteMatch(req, res)
})


//获取详情
//取比赛信息详情
const getMatchDetail = (req, res) => {
}
router.post('/getMatchDetail', (req, res) => {
    getMatchDetail(req, res)
})



//新增比赛数据详情
const queryDetail = (req) => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let { id } = req.body
        pool.query('select * from matchlist where id = ?;select * from matchdetail where id = ?', [id, id], (err, result) => {
            try {
                if (result[0]) {
                    resolve(result)
                } else {
                    resolve('error')
                }

            } catch {
                resolve('error')
            }
        })
    })
}
const updateDetail = (req) => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let sql = 'update matchdetail set homeGoal = ?,  homeAsstis = ?,homeRate = ?,homeDanger = ? ,guestGoal = ? ,guestAsstis = ?,guestRate = ? ,guestDanger = ? where id = ?'
        let { homeGoal, homeAsstis, homeRate, homeDanger, guestGoal, guestAsstis, guestRate, guestDanger, id } = req.body
        let queryArr = [homeGoal, homeAsstis, homeRate, homeDanger, guestGoal, guestAsstis, guestRate, guestDanger, id]
        pool.query(sql, queryArr, (err, result) => {
            try {
                if (result.affectedRows >= 1) {
                    resolve(true)
                }
                else {
                    resolve(false)
                }

            } catch {
                resolve(false)
            }
        })
    })
}


const updateMatch = (req) => {
    return new Promise((resolve, reject) => {
        let pool = mysql.createPool(env)
        let sql = 'update matchlist set homePoint = ?,guestPoint = ? where id = ?'
        let { homePoint, guestPoint, id } = req.body
        let queryArr = [homePoint, guestPoint, id]
        pool.query(sql, queryArr, (err, result) => {
            try {
                if (result.affectedRows >= 1) {
                    resolve(true)
                }
                else {
                    resolve(false)
                }

            } catch {
                resolve(false)
            }
        })
    })
}



//拆解 type 1 为进球 2为助攻
let splitArr = (str,type) => {
    return new Promise((resolve, reject) => {
        if(str){
            let splitArr = str.split('/')
            let arrs = []
            if(type==1){
                splitArr.forEach(ele => {
                    let index = ele.indexOf('-')
                    let obj = {
                        name: ele.slice(0, index),
                        goal: ele.slice(index + 1, ele.length)
                    }
                    arrs.push(obj)
                })
            }
            else{
                splitArr.forEach(ele => {
                    let index = ele.indexOf('-')
                    let obj = {
                        name: ele.slice(0, index),
                        ass: ele.slice(index + 1, ele.length)
                    }
                    arrs.push(obj)
                })
            }
            resolve(arrs)
            return
        }
        resolve([])
    })
}








const postMatchDetail = async (req, res) => {
    let resp1 = await updateDetail(req)
    let resp2 = await updateMatch(req)
    if (!resp1) {
        status500(res, { msg: '更新数据失败' })
    }
    if (!resp2) {
        status500(res, { msg: '更新数据失败' })
    }
    status200(res, { msg: "更新成功" })
}
router.post('/postMatchDetail', (req, res) => {
    postMatchDetail(req, res)
})


//更新数据(新增)
router.post('/updateGoal', (req, res) => {
    //1是主队 2是客队
    let { id,type,name,goal,year} = req.body
    let pool=mysql.createPool(env)
    pool.query('select * from matchlist where id = ?',[id],(err,result)=>{
        try{
         let team=type=='1'?result[0].homeId:result[0].guestId
         pool.query('select * from playerdetail where name = ? and team = ? and year =?',[name,team,year],(err,resp)=>{
            let now=Number(goal)+Number(resp[0]['goal'])
            pool.query('update playerdetail  set goal = ? where name = ? and team = ? and year = ?',[now,name,team,year],(err,resk)=>{
                try{
                    if(resk.affectedRows>=1){
                        status200(res,{msg:'更新成功'})
                    }
                    else{
                        status500(res,{msg:'更新失败'})
                    }
                }catch{
                    res.status(500)
                    console.log(err,'error')
                }
            })
         })
        }catch{
          res.status(500)
          console.log(err,'error')
        }
    })
})

//更新数据(删除)
router.post('/deleteGoal', (req, res) => {
    //1是主队 2是客队
    let { id,type,name,goal,year} = req.body
    let pool=mysql.createPool(env)
    pool.query('select * from matchlist where id = ?',[id],(err,result)=>{
        try{
         let team=type=='1'?result[0].homeId:result[0].guestId
         pool.query('select * from playerdetail where name = ? and team = ? and year =?',[name,team,year],(err,resp)=>{
            let now=Number(resp[0]['goal'])-Number(goal)
            pool.query('update playerdetail  set goal = ? where name = ? and team = ? and year = ?',[now,name,team,year],(err,resk)=>{
                try{
                    if(resk.affectedRows>=1){
                        status200(res,{msg:'更新成功'})
                    }
                    else{
                        status500(res,{msg:'更新失败'})
                    }
                }catch{
                    res.status(500)
                    console.log(err,'error')
                }
            })
         })
        }catch{
          res.status(500)
          console.log(err,'error')
        }
    })
})

//更新数据(助攻)
router.post('/updateAssist',(req,res)=>{
    //1是主队 2是客队
    let { id,type,name,ass,year} = req.body
    let pool=mysql.createPool(env)
    pool.query('select * from matchlist where id = ?',[id],(err,result)=>{
        try{
         let team=type=='1'?result[0].homeId:result[0].guestId
         pool.query('select * from playerdetail where name = ? and team = ? and year =?',[name,team,year],(err,resp)=>{
            let now=Number(resp[0]['assist'])+Number(ass)
            pool.query('update playerdetail  set assist = ? where name = ? and team = ? and year = ?',[now,name,team,year],(err,resk)=>{
                try{
                    if(resk.affectedRows>=1){
                        status200(res,{msg:'更新成功'})
                    }
                    else{
                        status500(res,{msg:'更新失败'})
                    }
                }catch{
                    res.status(500)
                    console.log(err,'error')
                }
            })
         })
        }catch{
          res.status(500)
          console.log(err,'error')
        }
    })
})

router.post('/deleteAssist',(req,res)=>{
    //1是主队 2是客队
    let { id,type,name,ass,year} = req.body
    let pool=mysql.createPool(env)
    pool.query('select * from matchlist where id = ?',[id],(err,result)=>{
        try{
         let team=type=='1'?result[0].homeId:result[0].guestId
         pool.query('select * from playerdetail where name = ? and team = ? and year =?',[name,team,year],(err,resp)=>{
            let now=Number(resp[0]['assist'])-Number(ass)
            pool.query('update playerdetail  set assist = ? where name = ? and team = ? and year = ?',[now,name,team,year],(err,resk)=>{
                try{
                    if(resk.affectedRows>=1){
                        status200(res,{msg:'更新成功'})
                    }
                    else{
                        status500(res,{msg:'更新失败'})
                    }
                }catch{
                    res.status(500)
                    console.log(err,'error')
                }
            })
         })
        }catch{
          res.status(500)
          console.log(err,'error')
        }
    })
})

//获取详情
const getMatchData=(req,res)=>{
    let {id}=req.query
    let pool=mysql.createPool(env)
    let sql='select * from matchlist where id = ?;select * from matchdetail where id = ?'
    pool.query(sql,[id,id],async(err,resl)=>{
        try{
            let guestAsstis=await splitArr(resl[1][0].guestAsstis,'2')
            let homeAsstis=await splitArr(resl[1][0].homeAsstis,'2')
            let homeGoal=await splitArr(resl[1][0].homeGoal,'1')
            let guestGoal=await splitArr(resl[1][0].guestGoal,'1')
            let data={
                homePoint:resl[0][0].homePoint,
                guestPoint:resl[0][0].guestPoint,
                homeDanger:resl[1][0].homeDanger,
                guestDanger:resl[1][0].guestDanger,
                homeRate:resl[1][0].homeRate,
                guestRate:resl[1][0].guestRate
            }
            status200(res,{msg:'success',data:{homeGoal:homeGoal,guestGoal:guestGoal,homeAsstis:homeAsstis,guestAsstis:guestAsstis,data:data}})
        }catch{
            res.status(500)
            console.log(err,'error')
        }
    })
}
router.get('/getMatchData',(req,res)=>{
    getMatchData(req,res)
})



module.exports = router